import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Scanner;

public class BookManager {
    static final Scanner sc = new Scanner(System.in);
    public void start() {
        try {
            menu();
        } catch (SQLException | ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 目录
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    private void menu() throws SQLException, ClassNotFoundException {
        System.out.println("****欢迎来到开发喵图书馆****");
        System.out.println("1. 添加图书");
        System.out.println("2. 删除图书");
        System.out.println("3. 修改图书");
        System.out.println("4. 显示所有");
        System.out.println("5. 显示所有日志");
        System.out.println("****请输入你要操作的选项****");
        int type = sc.nextInt();
        switch (type) {
            case 1 -> insert();
            case 2 -> deleteBook();
            case 3 -> updateBook();
            case 4 -> showAllBooks();
            case 5 -> showAllLog();
            default -> {
                System.out.println("退出程序");
                System.exit(1);
            }
        }
        menu();
    }
    /**
     * 加载数据包，连接数据库
     * @return
     */
    private Connection getCollection() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
        String url = "jdbc:mysql://localhost:3306/manger";
        try {
            return DriverManager.getConnection(url, "root", "");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 获取执行 sql 的对象
     * 执行sql
     *
     * @param sql
     * @return
     */
    private PreparedStatement executeUpdate(String sql){
        Connection conn = getCollection();
        try {
            PreparedStatement stat = conn.prepareStatement(sql);
            return stat;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 打开程序直接检测是否存在表
     * @throws SQLException
     */
    private BookManager(){
        try {
            init();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 检测是否存在 books 以及 log
     * @throws SQLException
     */
    private void init() throws SQLException {
        String sql = "show tables";
        Connection conn = getCollection();
        // 获取执行 sql 的对象
        Statement stat = conn.createStatement();
        // 执行sql
        ResultSet resultSet = stat.executeQuery(sql);
        List<String> list = new ArrayList<>();
        // 解析结果
        while (resultSet.next()) {
            list.add(resultSet.getString(1));
        }
        if (!(list.contains("books") && list.contains("log"))) {
            // 不存在就去读取文件
            String readSql = getSqlFile();
            String[] strings = readSql.split(";");
            for (String s : strings) {
                stat.executeUpdate(s);
            }
        }
    }

    /**
     * 切分sql文件
     * @return
     */
    private String getSqlFile(){
        try(FileInputStream in = new FileInputStream(new File("JDBC/days9.23Exam/sql/sql.txt"));){
            byte[] bytes = in.readAllBytes();
            return new String(bytes);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 添加书籍
     */
    private void insert() throws SQLException {
        String sql = "INSERT INTO books(book_title,publication_date,author,price,quantity) values (?,?,?,?,?)";
        String title;
        int i;
        try (PreparedStatement statement = executeUpdate(sql)) {
            System.out.println("请输入书名");
            title = sc.next();
            statement.setString(1, title);
            System.out.println("请输入出版日期");
            String date = sc.next();
            statement.setString(2, date);
            System.out.println("请输入作者");
            String author = sc.next();
            statement.setString(3, author);
            System.out.println("请输入价格");
            BigDecimal price = BigDecimal.valueOf(sc.nextDouble());
            statement.setBigDecimal(4, price);
            System.out.println("请输入数量");
            int quantity = sc.nextInt();
            statement.setInt(5, quantity);
            i = statement.executeUpdate();
        }
        if (i >= 1) {
            java.util.Date d = new java.util.Date();
            addLog("插入《" + title + "》", d);
            System.out.println(colorInfo("添加图书《" + title + "》成功", 32));
            }else {
                System.out.println(colorInfo("添加图书《" + title + "》失败", 31));
            }
        }

    /**
     * 删除书籍
     * @return
     */
    private void deleteBook(){
        String sql ="DELETE FROM books WHERE book_title = ?";
        System.out.println("请输入需要删除的书籍");
        String title = sc.next();
        String sql1 = "select * from books where book_title = '" + title + "'";
        try {
            List<Books> books = executeQuery(sql1);
            if (books.size() > 0) {
                try (PreparedStatement statement = executeUpdate(sql)) {
                    statement.setString(1,title);
                    int i = statement.executeUpdate();
                    if (i >= 1) {
                        System.out.println(colorInfo("删除图书《" + title + "》成功", 32));
                        addLog("删除《" + title + "》", new java.util.Date());
                    }else {
                        System.out.println(colorInfo("删除图书《" + title + "》失败", 31));
                    }
                }
            }else {
                System.out.println(colorInfo("你要删除的《" + title + "》不存在", 31));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 修改图书数量
     * @return
     */
    private void updateBook() throws SQLException {
        System.out.println("请输入需要修改的书籍");
        String title = sc.next();
        System.out.println("请输入修改数量（数量要⽐原值⼤）");
        int quantity = sc.nextInt();
        String sql1 = "select * from books where book_title = '" + title + "'";
        List<Books> books = executeQuery(sql1);
        int old = -1;
        if (books.size() > 0){
            old = books.get(0).getQuantity();
            if (old > quantity) {
                String sql = "update books set quantity = ? where book_title = ?";
                int i;
                try (PreparedStatement statement = executeUpdate(sql)) {
                    statement.setString(2, title);
                    statement.setInt(1, quantity);
                    i = statement.executeUpdate();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
                if (i > 0) {
                    System.out.println(colorInfo("修改《" + title + "》的数量成功",32));
                    addLog("修改" + title + "数量成功，原数量：" + old + "，新数量：" + quantity, new java.util.Date());
                } else {
                    System.out.println(colorInfo("修改《" + title + "》的数量失败",32));
                }
            }else {
                System.out.println(colorInfo("修改《" + title + "》数量错误， 输入的数量应该比原来的数量大", 31));
            }
        }else {
            System.out.println(colorInfo("你要修改的《" + title + "》不存在", 31));
        }
    }

    private List<Books> executeQuery(String sql) throws SQLException {
        Connection conn = getCollection();
        Statement stat = conn.createStatement();
        ResultSet set = stat.executeQuery(sql);
        List<Books> books = new ArrayList<>();
        while (set.next()){
            Integer id = set.getInt("id");
            String title = set.getString("book_title");
            String date = set.getString("publication_date");
            String author = set.getString("author");
            BigDecimal prince = set.getBigDecimal("price");
            int quantity = set.getInt("quantity");
            Books book = new Books(id,title,date,author,prince,quantity);
            books.add(book);
        }
        return books;
    }

    /**
     * 展示所有书籍
     */
    private void showAllBooks(){
        String sql = "select * from books";
        try {
            List<Books> books = executeQuery(sql);
            books.forEach(System.out::println);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 添加操作日志
     * @param description
     * @param date
     * @throws SQLException
     */
    private void addLog(String description, java.util.Date date) throws SQLException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String datetime = sdf.format(date);
        String sql = "insert into log(description, operation_time) values ('" + description + "', '" + datetime + "')";
        PreparedStatement stat = executeUpdate(sql);
        int i = stat.executeUpdate();
        if (i > 0) {
            System.out.println(colorInfo("插入日志成功", 32));
        } else {
            System.out.println(colorInfo("插入日志失败", 31));
        }
    }

    private <E> List<E> execute(String sql, Handler<E> handler) throws SQLException, ClassNotFoundException {
        Connection conn = getCollection();
        Statement stat = conn.createStatement();
        ResultSet resultSet = stat.executeQuery(sql);
        List<E> list = new ArrayList<>();
        int count = resultSet.getMetaData().getColumnCount();
        Object[] objects = new Object[count];
        while (resultSet.next()) {
            // 构建出一个数组  数组里面放的是一行数据
            for (int i = 0; i < count; i++) {
                objects[i] = resultSet.getObject(i + 1);
            }

            E one = handler.getOne(objects);
            list.add(one);
        }
        return list;
    }


    public void test() throws SQLException, ClassNotFoundException {
        List<Books> execute = execute("select * from books", new Handler<Books>() {
            @Override
            public Books getOne(Object... objects) {
                Books book = new Books();
                book.setId((int) objects[0]);
                book.setTitle((String) objects[1]);
                book.setDate(String.valueOf(objects[2]));
                book.setAuthor((String) objects[3]);
                book.setPrice((BigDecimal) objects[4]);
                book.setQuantity((Integer) objects[5]);
                return book;
            }
        });

        List<Log> execute1 = execute("select * from log", new Handler<Log>() {
            @Override
            public Log getOne(Object... objects) {
                Log log = new Log();
                log.setLogId((Integer) objects[0]);
                log.setDescription((String) objects[1]);
                log.setOperationTime((Timestamp) objects[2]);
                return log;
            }
        });
    }
    private void showAllLog (){

    }

    private String colorInfo(String content, int color) {
        return String.format("\033[%dm%s\033[0m", color, content);
    }

    public static void main(String[] args) {
        BookManager book = new BookManager();
        book.start();
    }
}


